package com.hpe.service;

import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;

public class ExcelReadService {
    // 判断excel版本
    static Workbook openWorkbook(String filename, String fileFileName) throws IOException {
        InputStream in = new FileInputStream(filename); // 创建输入流
        Workbook wb = null;
        if (fileFileName.endsWith(".xlsx")) {
            try {
                wb = new XSSFWorkbook(in);// Excel 2007
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            wb = (Workbook) new HSSFWorkbook(in);// Excel 2003
        }
        return wb;
    }

    public static void getExcelDataToXml(String fileName, String fileFileName) throws Exception {
        System.out.println("开始读取excel:" + fileName);
        Workbook wb = openWorkbook(fileName, fileFileName);// 获取Excel文件对象
        System.out.println("读取完毕！excel:" + fileName);
        Sheet sheet = wb.getSheetAt(0);// 获取文件的指定工作表m 默认的第一个
        String colsName = new String();
        String[] colsNames = new String[10];

        Cell cell = null;
        Row row = sheet.getRow(0);
        // 总行数
        int rows = sheet.getPhysicalNumberOfRows();
        // 总列数
        int cols = row.getPhysicalNumberOfCells();

        for (int i = 0; i < cols; i++) {
            cell = row.getCell(i);
            if (null != cell) {
                // 以下是判断数据的类型
                switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC: // 数字
                    colsName = cell.getNumericCellValue() + "";
                    break;

                case XSSFCell.CELL_TYPE_STRING: // 字符串
                    colsName = cell.getStringCellValue();
                    break;

                case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    colsName = cell.getBooleanCellValue() + "";
                    break;

                case XSSFCell.CELL_TYPE_FORMULA: // 公式
                    colsName = cell.getCellFormula() + "";
                    break;

                case XSSFCell.CELL_TYPE_BLANK: // 空值
                    colsName = "";
                    break;

                case XSSFCell.CELL_TYPE_ERROR: // 故障
                    colsName = "非法字符";
                    break;

                default:
                    colsName = "未知类型";
                    break;
                }
                colsNames[i] = colsName;
            }
        }
        Document document = DocumentHelper.createDocument();
        // 增加根节点
        Element root = DocumentHelper.createElement("成员资料");
        document.setRootElement(root);
        for (int i = 1; i < rows; i++) {
            Row row1 = sheet.getRow(i);
            Element element1 = root.addElement("成员列表");
            for (int j = 0; j < cols; j++) {
                Cell cell1 = row1.getCell(j);
                Element element2 = element1.addElement(colsNames[j]);

                if (null != cell1) {
                    // 以下是判断数据的类型
                    switch (cell1.getCellType()) {
                    case XSSFCell.CELL_TYPE_NUMERIC: // 数字
                        colsName = cell1.getNumericCellValue() + "";
                        break;

                    case XSSFCell.CELL_TYPE_STRING: // 字符串
                        colsName = cell1.getStringCellValue();
                        break;

                    case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                        colsName = cell1.getBooleanCellValue() + "";
                        break;

                    case XSSFCell.CELL_TYPE_FORMULA: // 公式
                        colsName = cell1.getCellFormula() + "";
                        break;

                    case XSSFCell.CELL_TYPE_BLANK: // 空值
                        colsName = "";
                        break;

                    case XSSFCell.CELL_TYPE_ERROR: // 故障
                        colsName = "非法字符";
                        break;

                    default:
                        colsName = "未知类型";
                        break;
                    }
                }
                element2.setText(colsName);
            }
        }
        System.out.println("读取excel" + fileFileName + "成功");
        OutputFormat format = new OutputFormat("    ", true);
        // 生成XML文件的路径和名称
        XMLWriter xmlWriter = new XMLWriter(
                new FileWriter(new File(fileName.substring(0, fileName.length() - 5) + ".xml")));
        System.out.println("===========生成XML文件成功==========");
        xmlWriter.write(document);
        xmlWriter.close();
    }

    public static void main(String args[]) {
        try {
            ImapReceiveMailService imapReceiveMailService =new ImapReceiveMailService();
            imapReceiveMailService.getInboxMessage();
//            ExcelReadService.getExcelDataToXml("c:\\mailtmp\\20151203100110_Subject_成绩表.xlsx", ".xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}